package com.sirdc.modules.sys.dao;

import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.sys.entity.SysUserLogin;
import com.sirdc.modules.sys.filter.SysUserLoginFilter;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class SysUserLoginDao extends StringDao<SysUserLogin> {
    public List<SysUserLogin> queryUserOnlineCount(){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT name,num,CASE WHEN @rowtotal = obj.NUM THEN  @rownum WHEN @rowtotal \\:= obj.NUM THEN @rownum \\:=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum \\:=@rownum + 1 END AS rownum FROM (SELECT u. NAME NAME, count(l. NAME) num FROM t_sys_user u LEFT JOIN t_sys_user_login l ON u.`name` = l.`name` GROUP BY u. NAME ORDER BY num DESC) AS obj,(SELECT @rownum \\:= 0 ,@rowtotal \\:= NULL) r ");
        SQLQuery query = getSession().createSQLQuery(sql.toString())
                .addScalar("name", StandardBasicTypes.STRING)
                .addScalar("num", StandardBasicTypes.INTEGER)
                .addScalar("rownum", StandardBasicTypes.INTEGER);
        query.setResultTransformer(Transformers.aliasToBean(SysUserLogin.class));
        List<SysUserLogin> result = (List<SysUserLogin>) query.list();
        return result;
    }

    public List<SysUserLogin> queryUserOnlineList(SysUserLoginFilter filter){
        StringBuffer sqlcount = new StringBuffer();
        sqlcount.append("SELECT count(DISTINCT(name)) FROM t_sys_user ");
        SQLQuery querycount = getSession().createSQLQuery(sqlcount.toString());
        List resultcount = querycount.list();
        int total = Integer.parseInt(resultcount.get(0).toString());
        filter.getPaging().setPageCount(total/filter.getPaging().getPageSize() + 1);
        filter.getPaging().setRecords(total);
        StringBuffer sql = new StringBuffer();
        int fristnum = (filter.getPaging().getCurrentPage() - 1) * filter.getPaging().getPageSize();
        int endnum = fristnum + filter.getPaging().getPageSize();
        sql.append("SELECT name,num,CASE WHEN @rowtotal = obj.NUM THEN  @rownum WHEN @rowtotal \\:= obj.NUM THEN @rownum \\:=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum \\:=@rownum + 1 END AS rownum FROM (SELECT u. NAME NAME, count(l. NAME) num FROM t_sys_user u LEFT JOIN t_sys_user_login l ON u.`name` = l.`name` GROUP BY u. NAME ORDER BY num DESC) AS obj,(SELECT @rownum \\:= 0 ,@rowtotal \\:= NULL) r limit "+fristnum+"," + endnum);
        SQLQuery query = getSession().createSQLQuery(sql.toString())
                .addScalar("name", StandardBasicTypes.STRING)
                .addScalar("num", StandardBasicTypes.INTEGER)
                .addScalar("rownum", StandardBasicTypes.INTEGER);
        query.setResultTransformer(Transformers.aliasToBean(SysUserLogin.class));
        List<SysUserLogin> result = (List<SysUserLogin>) query.list();
        return result;
    }
}
